import datetime
import json

from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import Integer
from sqlalchemy.orm import Mapped, mapped_column

from enums.AppEnum import AppEnum

# 先创建 db 对象（不要在此处初始化）
db = SQLAlchemy()


# 逻辑删除基类
class SoftDeleteModel:
    '''逻辑删除基类'''
    isDelete: Mapped[int] = mapped_column(Integer, default=0)

    @classmethod
    def query_active(cls):
        return db.session.query(cls)  #.filter(cls.isDelete == 0)

    @classmethod
    def restore_user(cls, id):
        item = db.session.query(cls).filter_by(id=id).first()
        if item:
            item.isDelete = 0
            db.session.commit()
            return True
        return False


class User(db.Model, SoftDeleteModel):
    __tablename__ = 'user'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    username = db.Column(db.String(20), unique=True, nullable=False)
    password = db.Column(db.String(32))
    nickname = db.Column(db.String(10))
    email = db.Column(db.String(128))
    user_pic = db.Column(db.String(128))
    create_time = db.Column(db.DateTime, default=datetime.datetime.now)
    update_time = db.Column(db.DateTime, default=datetime.datetime.now, onupdate=datetime.datetime.now)
    isDelete = db.Column(db.Integer, default=0)
    user_role = db.Column(db.String(256), default='user', nullable=False)
    user_profile = db.Column(db.String(256), default='')

    def __repr__(self):
        return f"<User> {self.username}\t <self.id> {self.id}>\t <self.nickname> {self.nickname}>\t<self.create_time>{self.create_time}\n"

    def to_dict(self):
        """
        将 User 对象转换为字典
        """
        return {
            'id': self.id,
            'username': self.username,
            "nickname": self.nickname,
            'email': self.email,
            'user_pic': self.user_pic,
            'create_time': self.create_time.strftime("%Y-%m-%d %H:%M:%S"),
            'update_time': self.update_time.strftime("%Y-%m-%d %H:%M:%S"),
            'isDelete': self.isDelete,
            'user_role': self.user_role,
            'user_profile': self.user_profile
            # 根据实际的 User 类字段添加更多键值对
        }


class Category(db.Model):
    __tablename__ = 'category'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    category_name = db.Column(db.String(20), unique=True)
    category_alias = db.Column(db.String(20), unique=True)
    create_user = db.Column(db.Integer, db.ForeignKey('user.id', ondelete='CASCADE'))
    create_time = db.Column(db.DateTime, default=datetime.datetime.now)
    update_time = db.Column(db.DateTime, default=datetime.datetime.now, onupdate=datetime.datetime.now)
    articles = db.relationship('Article', backref='category', lazy=True, cascade="all, delete-orphan")

    def __repr__(self):
        return f"<id>{self.id} <category_name> {self.category_name} <alias> {self.category_alias} <create_time> {self.create_time}\n"


class Article(db.Model):
    __tablename__ = 'article'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    title = db.Column(db.String(30))
    content = db.Column(db.String(10000))
    cover_img = db.Column(db.String(128))
    state = db.Column(db.String(3))
    category_id = db.Column(db.Integer, db.ForeignKey('category.id', ondelete='CASCADE'))
    create_user = db.Column(db.Integer, db.ForeignKey('user.id', ondelete='CASCADE'))
    create_time = db.Column(db.DateTime, default=datetime.datetime.now)
    update_time = db.Column(db.DateTime, default=datetime.datetime.now, onupdate=datetime.datetime.now)


class App(db.Model, SoftDeleteModel):
    __tablename__ = 'app'

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    appName = db.Column(db.String(128), nullable=False)
    appDesc = db.Column(db.String(2048))
    appIcon = db.Column(db.String(1024))
    appType = db.Column(db.SmallInteger, default=0, nullable=False)
    scoringStrategy = db.Column(db.SmallInteger, default=0, nullable=False)
    reviewStatus = db.Column(db.Integer, default=0, nullable=False)
    reviewMessage = db.Column(db.String(512))
    reviewerId = db.Column(db.Integer)
    reviewTime = db.Column(db.DateTime)
    userId = db.Column(db.Integer, db.ForeignKey("user.id"), nullable=False)
    createTime = db.Column(db.DateTime, default=datetime.datetime.now(), nullable=False)
    updateTime = db.Column(db.DateTime, default=datetime.datetime.now(), onupdate=datetime.datetime.now(),
                           nullable=False)
    isDelete = db.Column(db.SmallInteger, default=0, nullable=False)

    def to_json(self):
        return {
            "id": self.id,
            "appName": self.appName,
            'appDesc': self.appDesc,
            'appIcon': self.appIcon,
            'appType': self.appType,
            'scoringStrategy': self.scoringStrategy,
            'reviewStatus': self.reviewStatus,
            'reviewerId': self.reviewerId,
            'reviewMessage':self.reviewMessage,
            'reviewTime': self.reviewTime.strftime("%Y-%m-%d %H:%M:%S") if self.reviewTime is not None else None,
            'createTime': self.createTime.strftime("%Y-%m-%d %H:%M:%S"),
            'updateTime': self.updateTime.strftime("%Y-%m-%d %H:%M:%S"),
            'isDelete': self.isDelete,
        }

    def to_json_admin(self):
        app_info = self.to_json()
        userId = self.userId
        user = db.session.query(User).filter(userId == User.id).first()
        app_info['user'] = user.to_dict()
        return app_info

    def to_json_VO(self):
        app_info = self.to_json()
        userId = self.userId
        user = db.session.query(User).filter(userId == User.id).first()
        app_info['user'] = user.to_dict()
        return app_info


class Question(db.Model, SoftDeleteModel):
    __tablename__ = 'question'

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    questionContent = db.Column(db.Text)
    appId = db.Column(db.Integer, nullable=False)
    userId = db.Column(db.Integer, nullable=False)
    createTime = db.Column(db.DateTime, default=datetime.datetime.now(), nullable=False)
    updateTime = db.Column(db.DateTime, default=datetime.datetime.now(), onupdate=datetime.datetime.now(),
                           nullable=False)
    isDelete = db.Column(db.SmallInteger, default=0, nullable=False)

    def question_admin_to_json_VO(self):
        question_info = {
            "id": self.id,
            "questionContent": self.questionContent,
            "appId": self.appId,
            "userId": self.userId,
            "createTime": self.createTime.strftime("%Y-%m-%d %H:%M:%S"),
            "updateTime": self.updateTime.strftime("%Y-%m-%d %H:%M:%S"),
            "isDelete": self.isDelete,
        }
        # userId = self.userId
        # user = db.session.query(User).filter(userId == User.id).first()
        # question_info['user'] = user.to_dict()
        return question_info

    def question_user_to_json_VO(self):
        question_info = {
            "id": self.id,
            "questionContent": self.questionContent,
            "appId": self.appId,
            "userId": self.userId,
            "createTime": self.createTime.strftime("%Y-%m-%d %H:%M:%S"),
            "updateTime": self.updateTime.strftime("%Y-%m-%d %H:%M:%S"),
        }
        userId = self.userId
        user = db.session.query(User).filter(userId == User.id).first()
        question_info['user'] = user.to_dict()
        return question_info


class ScoringResult(db.Model, SoftDeleteModel):
    __tablename__ = 'scoring_result'

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    resultName = db.Column(db.String(128), nullable=False)
    resultDesc = db.Column(db.Text)
    resultPicture = db.Column(db.String(1024))
    resultProp = db.Column(db.String(128))
    resultScoreRange = db.Column(db.Integer)
    appId = db.Column(db.Integer, db.ForeignKey("user.id"),nullable=False)
    userId = db.Column(db.Integer, db.ForeignKey("app.id"),nullable=False)
    createTime = db.Column(db.DateTime, default=datetime.datetime.now(), nullable=False)
    updateTime = db.Column(db.DateTime, default=datetime.datetime.now(), onupdate=datetime.datetime.now(),
                           nullable=False)
    isDelete = db.Column(db.SmallInteger, default=0, nullable=False)

    user = db.relationship(User)
    app = db.relationship(App, backref="scoringResults")

    def to_json(self):
        base_data = {
            "appId": self.appId,
            "createTime": self.createTime.strftime("%d/%m/%Y %H:%M:%S"),
            "id": self.id,
            "isDelete": self.isDelete,
            "resultName":self.resultName,
            "resultDesc": self.resultDesc,
            "resultScoreRange":self.resultScoreRange,
            "resultProp":self.resultProp,
            "resultPicture": self.resultPicture,
            "updateTime": self.updateTime.strftime("%d/%m/%Y %H:%M:%S")
        }
        app_type = self.app.appType
        if app_type == AppEnum.Scoring_Category.value:  # 得分类的app
            base_data['resultScoreRange'] = self.resultScoreRange
        elif app_type == AppEnum.Evaluation_Category.value:  # 测评类的app
            base_data['resultProp'] = self.resultProp

        return base_data

    def Scoring_result_to_json_VO(self):
        base_data = self.to_json()
        user = db.session.query(User).filter(User.id == self.userId).first()
        base_data['user'] = user.to_dict()
        return base_data

    def Scoring_result_to_json_admin(self):
        base_data = self.to_json()
        base_data['userId'] = self.userId
        return base_data


class UserAnswer(db.Model, SoftDeleteModel):
    __tablename__ = 'user_answer'

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    appId = db.Column(db.Integer,db.ForeignKey("app.id"), nullable=False)
    appType = db.Column(db.SmallInteger, default=0, nullable=False)
    scoringStrategy = db.Column(db.SmallInteger, default=0, nullable=False)
    choices = db.Column(db.Text)
    resultId = db.Column(db.Integer,db.ForeignKey("scoring_result.id"))
    resultName = db.Column(db.String(128))
    resultDesc = db.Column(db.Text)
    resultPicture = db.Column(db.String(1024))
    resultScore = db.Column(db.Integer)
    userId = db.Column(db.Integer, db.ForeignKey("user.id"),nullable=False)
    createTime = db.Column(db.DateTime, default=datetime.datetime.now(), nullable=False)
    updateTime = db.Column(db.DateTime, default=datetime.datetime.now(), onupdate=datetime.datetime.now(),
                           nullable=False)
    isDelete = db.Column(db.SmallInteger, default=0, nullable=False)
    # 添加关系
    scoring_result = db.relationship("ScoringResult", backref="user_answers")
    user = db.relationship("User", backref="user_answers")
    app = db.relationship("App", backref="user_answers")

    def to_user_json_vo(self):
        return {
            "id": self.id,
            "appId": self.appId,
            "appType": self.appType,
            "scoringStrategy": self.scoringStrategy,
            "choice": self.choices.split(","),
            "resultId": self.resultId,
            "resultName": self.resultName,
            "resultDesc": self.resultDesc,
            "resultPicture": self.resultPicture,
            "resultScore": self.resultScore,
            "userId": self.userId,
            "createTime": self.createTime.strftime("%d/%m/%Y %H:%M:%S"),
            "updateTime": self.updateTime.strftime("%d/%m/%Y %H:%M:%S"),
            "user": self.user.to_dict(),
            "isDelete": self.isDelete,
        }

    def to_admin_json_vo(self):
        return {
            "id": self.id,
            "appId": self.appId,
            "appType": self.appType,
            "scoringStrategy": self.scoringStrategy,
            "choice": self.choices.split(","),
            "resultId": self.resultId,
            "resultName": self.resultName,
            "resultDesc": self.resultDesc,
            "resultPicture": self.resultPicture,
            "resultScore": self.resultScore,
            "userId": self.userId,
            "createTime": self.createTime.strftime("%d/%m/%Y %H:%M:%S"),
            "updateTime": self.updateTime.strftime("%d/%m/%Y %H:%M:%S"),
            "isDelete": self.isDelete,
        }


class File(db.Model, SoftDeleteModel):
    __tablename__ = 'file'

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    file_name =db.Column(db.String(50), unique=True, nullable=False)
    isIndex = db.Column(db.SmallInteger, default=0, nullable=False)
    create_user = db.Column(db.Integer, db.ForeignKey("user.id"), nullable=False)
    create_time = db.Column(db.DateTime,  default=datetime.datetime.now(), nullable=False)
    # 假设SoftDeleteModel已经处理了isDelete字段
    # 如果SoftDeleteModel没有处理，可以添加：
    isDelete = db.Column(db.SmallInteger, default=0, nullable=False)

    # 添加关系
    user = db.relationship("User", backref="files")

    def to_vo(self):
        return {
            "id": self.id,
            "file_name": self.file_name,
            "isIndex": self.isIndex,
            "create_time": self.create_time.strftime("%d/%m/%Y %H:%M:%S"),
            "isDelete": self.isDelete,
        }